﻿using System;
using System.Collections.Generic;
using NTTDATA.BEENChina.ResultCertification.model;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace NTTDATA.BEENChina.ResultCertification.DataStore
{
    public class CheckSettingAccess
    {
        private static string connStr
        {
            get {
                return System.Configuration.ConfigurationManager.AppSettings["ResultCertificationDB"]; ;
            }
        }


       

        public  void SaveSettings(CheckSetting setting)
        {
            SqlConnection con = new SqlConnection(connStr);
            con.Open();
            SqlTransaction tra = con.BeginTransaction();
            try
            {                
                updateSetting(tra,setting);
                //updateCheckTypes(tra,setting);
                VerfityAccess access = new VerfityAccess();
                foreach (VerfityItem item in setting.VerifyList)
                {
                    access.UpdateSelectVerfityItem(item.VerfityItemId,item.Selected);
                }
                tra.Commit();
            }
            catch (Exception exp)
            {
                tra.Rollback();
                throw exp;
            }
            finally
            {
                con.Close();
            }
        }

        private  void updateSetting(SqlTransaction tra,CheckSetting setting)
        {
            string sql = "if exists(SELECT * FROM CheckSetting) " +
                    " begin " +                        
                        " UPDATE CheckSetting SET BeginDeliveryTime=@beginTime,ENDDeliveryTime=@endTime,RunTIme=@runTime " +
                    " end " +
                " else " +
                    " begin " +
                        " INSERT CheckSetting SELECT @beginTime,@endTime,@runTime " +
                    " end ";

            SqlParameter[] sqlParameters = 
            {
                new SqlParameter("@beginTime", setting.BeginDeliveryTime),                
                new SqlParameter("@endTime", setting.EndDeliveryTime),                
                new SqlParameter("@runTime", setting.RunTime)                
            };

            SqlHelper.ExecuteNonQuery(tra, System.Data.CommandType.Text, sql, sqlParameters);
        }

        public int AddRunningState(DateTime runtime)
        {
            string sql = " INSERT INTO [RunningLog]([Runtime]) VALUES (@Runtime) ";
            SqlParameter[] sqlParameters = 
            {
                new SqlParameter("@Runtime", runtime)
            };

            return  SqlHelper.ExecuteNonQuery( connStr, System.Data.CommandType.Text, sql, sqlParameters);

        }

        public bool IsRunning(DateTime runtime)
         {
             string sql = " SELECT COUNT(*) FROM  [RunningLog] WHERE [Runtime] > @Runtime ";
             SqlParameter[] sqlParameters = 
            {
                new SqlParameter("@Runtime", runtime)
            };

             string isRunning = SqlHelper.ExecuteScalar(connStr, System.Data.CommandType.Text, sql, sqlParameters).ToString();

             return (int.Parse(isRunning) > 0);
         }


        //----------------------------------
        public  void AddCheckSetting( CheckSetting setting)
        {
            string sql = "  INSERT INTO [CheckSetting] "+
                        "   ( [BeginDeliveryTime],[EndDeliveryTime] ,[RunTime],[RunFlag] )    "+
                        "   VALUES  "+
                        "   ( @beginTime,@endTime,@runTime,0 ) ";
                   

            SqlParameter[] sqlParameters = 
            {
                new SqlParameter("@beginTime", setting.BeginDeliveryTime),                
                new SqlParameter("@endTime", setting.EndDeliveryTime),                
                new SqlParameter("@runTime", setting.RunTime)                
            };

            SqlHelper.ExecuteNonQuery(connStr, System.Data.CommandType.Text, sql, sqlParameters);
        }

        public void UpdateRunningFlag(CheckSetting setting)
        {
            string sql = "  UPDATE  [CheckSetting] "+
                        "       SET  [RunFlag] = @RunFlag" +
                        "   WHERE  [BeginDeliveryTime] =  @beginTime AND "+
                        "           [EndDeliveryTime] =  @endTime";


            SqlParameter[] sqlParameters = 
            {
                new SqlParameter("@beginTime", setting.BeginDeliveryTime),                
                new SqlParameter("@endTime", setting.EndDeliveryTime),                
                new SqlParameter("@runTime", setting.RunTime),
                new SqlParameter("@RunFlag",setting.RunFlag) 
                
            };

            SqlHelper.ExecuteNonQuery(connStr, System.Data.CommandType.Text, sql, sqlParameters);
        }

        public void DelCheckSetting(string id)
        {
            string sql = "  DELETE FROM [CheckSetting]    WHERE  [ID] =  @id ";
             

            SqlParameter[] sqlParameters = 
            {
                new SqlParameter("@id", id)             
            };

            SqlHelper.ExecuteNonQuery(connStr, System.Data.CommandType.Text, sql, sqlParameters);
        }


        public void SaveSelectVerfityItem(List<VerfityItem> verfityItems)
        {
            VerfityAccess access = new VerfityAccess();
            foreach (VerfityItem item in verfityItems)
            {
                access.UpdateSelectVerfityItem(item.VerfityItemId, item.Selected);
            }
        }

        

        public List<CheckSetting> GetCheckSettings()
        {
             List<CheckSetting> settings = null;

            string sql = "SELECT [ID], [BeginDeliveryTime],[EndDeliveryTime] ,[RunTime],[RunFlag]" +
                         " FROM CheckSetting " +
                         " ORDER BY [ID] DESC";

            using (SqlDataReader reader = SqlHelper.ExecuteReader(connStr, System.Data.CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    var  setting = new CheckSetting();

                    setting.ID = reader["ID"].ToString();
                    setting.BeginDeliveryTime = (DateTime)reader["BeginDeliveryTime"];
                    setting.EndDeliveryTime = (DateTime)reader["EndDeliveryTime"]; ;
                    setting.RunTime = (DateTime)reader["RunTime"]; ;
                    setting.RunFlag = reader["RunFlag"] == DBNull.Value ? 0 : (int) reader["RunFlag"];
                    setting.VerifyList = null;

                    if (settings == null)
                        settings = new List<CheckSetting>();

                    settings.Add(setting);
                }
            }

            return settings;
        }


        public  CheckSetting GetCheckSetting()
        {
            CheckSetting setting = new CheckSetting();

            string sql = "SELECT TOP 1 [ID], [BeginDeliveryTime],[EndDeliveryTime] ,[RunTime],[RunFlag]" +
                         " FROM CheckSetting " +
                         " WHERE ISNULL([RunFlag],0) = 0 " +
                         " ORDER BY [RunTime]";
            using (SqlDataReader reader = SqlHelper.ExecuteReader(connStr, System.Data.CommandType.Text, sql))
            {
                if (reader.Read())
                {
                    setting = new CheckSetting();

                    setting.ID = reader["ID"].ToString();
                    setting.BeginDeliveryTime = (DateTime)reader["BeginDeliveryTime"];
                    setting.EndDeliveryTime = (DateTime)reader["EndDeliveryTime"]; ;
                    setting.RunTime = (DateTime)reader["RunTime"]; ;
                    setting.VerifyList = null;
                }
            }

            return setting;
        }

    }
}
